package com.muc.plugin.dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.XMPPServer;
import org.jivesoftware.openfire.muc.MUCRole;
import org.jivesoftware.openfire.muc.MUCRoom;
import org.jivesoftware.openfire.muc.spi.MUCPersistenceManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;

/**
 * @author lyusantu@gmail.com
 */
public class MUCDao {

    private static final Logger log = LoggerFactory.getLogger(MUCDao.class);

    public static StringBuilder sqlBuilder = new StringBuilder();

    public static StringBuilder EXISTS_JID = new StringBuilder();

    public static StringBuilder ROOM_MEMBERS = new StringBuilder();

    private static final String STORAGE_USER_NICKNAME = "INSERT INTO ofmucnickname(userjid, roomjid, nickname, createtime) VALUES(?, ?, ?, ?)";

    private static final String REMOVE_USER_NICKNAME = "DELETE FROM ofmucnickname WHERE userjid = ? AND roomjid = ?";

    private static final String REMOVE_GROUP_MEMBERS_NICKNAME = "DELETE FROM ofmucnickname WHERE roomjid = ?";

    private static final String GET_MEMBER_GROUP_NICKNAME = "SELECT nickname FROM ofmucnickname WHERE userjid = ? AND roomjid = ?";

    static {
        sqlBuilder.append("SELECT mr.serviceID, mr.name, mr.roomid, mm.nickname FROM ");
        sqlBuilder.append(" ofmucroom mr JOIN ofmucmember mm ON mr.roomID = mm.roomID AND mm.jid = ?");
        sqlBuilder.append(" UNION");
        sqlBuilder.append(" SELECT mr.serviceID, mr.name, mr.roomid, NULL FROM");
        sqlBuilder.append(" ofmucroom mr JOIN ofmucaffiliation mf ON mr.roomID = mf.roomID AND mf.jid = ?");

        EXISTS_JID.append("SELECT mr.serviceID, mr.name, mr.roomid ,mm.nickname FROM");
        EXISTS_JID.append(" ofmucroom mr JOIN ofmucmember mm ON mr.roomID = mm.roomID AND mm.jid = ? AND mr.name=?");
        EXISTS_JID.append(" UNION");
        EXISTS_JID.append(" SELECT mr.serviceID, mr.name, mr.roomid ,null FROM");
        EXISTS_JID.append(" ofmucroom mr JOIN ofmucaffiliation mf ON mr.roomID = mf.roomID AND mf.jid = ? AND mr.name=?");

        ROOM_MEMBERS.append("SELECT mr.serviceID, mr.name, mr.roomid, mm.nickname FROM ");
        ROOM_MEMBERS.append(" ofmucroom mr JOIN ofmucmember mm ON mr.roomID = mm.roomID AND mr.name=?");
        ROOM_MEMBERS.append(" UNION");
        ROOM_MEMBERS.append(" SELECT mr.serviceID, mr.name, mr.roomid ,null FROM");
        ROOM_MEMBERS.append(" ofmucroom mr JOIN ofmucaffiliation mf ON mr.roomID = mf.roomID AND mr.name=?");
    }

    /**
     * 获取用户加入的群
     *
     * @param jid 用户JID
     * @return
     */
    public static List<Map<String, String>> getMUCInfo(String jid) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Map<String, String> map = null;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(sqlBuilder.toString());
            statement.setString(1, jid);
            statement.setString(2, jid);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                map = new HashMap<>();
                map.put("serviceID", resultSet.getString(1));
                map.put("name", resultSet.getString(2));
                map.put("roomId", resultSet.getString(3));
                map.put("nickName", resultSet.getString(4));
                list.add(map);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement, connection);
        }
        log.info("@@@@@@@@@ 用户加入的群信息 用户信息:" + jid + "list :" + list);
        return list;
    }


    /**
     * 检测用戶是否已经是群成员或者管理员
     *
     * @param jid 用戶JID
     * @return
     */
    public static boolean exists(String jid, String roomName) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(EXISTS_JID.toString());
            statement.setString(1, jid);
            statement.setString(2, roomName);
            statement.setString(3, jid);
            statement.setString(4, roomName);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return true;
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        log.info("MUCDAO exists:" + jid + ":" + roomName + ">false");
        return false;
    }


    /**
     * 检测jid是否已经是群成员 或者管理员
     *
     * @return
     */
    public static boolean existsMember(String roomName) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(ROOM_MEMBERS.toString());
            statement.setString(1, roomName);
            statement.setString(2, roomName);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return true;
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        log.info("@@@@@@@@@@ 检测jid是否已经是群成员 existsMember:" + roomName + ">false");
        return false;
    }

    /**
     * 获取 roomId 的 群基本信息
     *
     * @return
     */
    public static Map<String, String> getMUC(String roomJid) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Map<String, String> map = null;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement("select serviceID,roomId,name from ofmucroom where name=?");
            statement.setString(1, roomJid);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                map = new HashMap<>();
                map.put("serviceID", resultSet.getString(1));
                map.put("roomId", resultSet.getString(2));
                map.put("name", resultSet.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        log.info("@@@@@@@@@@@  群基本信息 get:" + roomJid + ":" + map);
        return map;
    }

    /**
     * 获取 roomid 的 群基本信息
     *
     * @return
     */
    public static HashSet<String> getMUCsMembers(List<String> roomIds) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //拼SQL
        StringBuffer sqlSB = new StringBuffer("select jid from ofmucmember where roomID in (");
        for (int x = 0; x < roomIds.size() - 1; x++) {
            sqlSB.append("?,");
        }
        sqlSB.append("?)");
        String sql = sqlSB.toString();
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(sql);
            int x = 1;
            for (String s : roomIds) {
                statement.setString(x, s);
                x++;
            }
            resultSet = statement.executeQuery();
            HashSet<String> item = new HashSet<String>();
            while (resultSet.next()) {
                String jid = resultSet.getString(1);
                item.add(jid.substring(0, jid.indexOf("@")).trim());
            }
            return item;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        return null;
    }

    /**
     * 修改 房间加入成员的昵称
     *
     * @param roomid 房间id
     * @param jid    jid
     * @param nick   昵称
     * @return
     */
    public static Boolean updateNick(Long roomid, String jid, String nick) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement("update ofmucmember set nickname = ? where roomID = ? and jid=?");
            pstmt.setString(1, nick);
            pstmt.setLong(2, roomid);
            pstmt.setString(3, jid);
            // 执行SQL
            return pstmt.executeUpdate() >= 0;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("update room nick error：" + e.getMessage());
        } finally {
            DbConnectionManager.closeConnection(null, pstmt, con);
        }
        return false;
    }

    /**
     * 存储用户群昵称
     */
    public static Boolean storageUserNickName(String userJid, String roomJid, String nickName) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(STORAGE_USER_NICKNAME);
            pstmt.setString(1, userJid);
            pstmt.setString(2, roomJid);
            pstmt.setString(3, nickName);
            pstmt.setDate(4, new Date(System.currentTimeMillis()));
            // 执行SQL
            return pstmt.executeUpdate() >= 0;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("storageUserNickName error：" + e.getMessage());
        } finally {
            DbConnectionManager.closeConnection(null, pstmt, con);
        }
        return false;
    }

    /**
     * 移除用户群昵称
     */
    public static Boolean removeUserNickName(String userId, String roomId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbConnectionManager.getConnection();
            pstmt = conn.prepareStatement(REMOVE_USER_NICKNAME);
            pstmt.setString(1, userId);
            pstmt.setString(2, roomId);
            return pstmt.executeUpdate() >= 0;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("removeUserNickName error：" + e.getMessage());
        } finally {
            DbConnectionManager.closeConnection(null, pstmt, conn);
        }
        return false;
    }

    /**
     * 解散群移除用户群昵称
     */
    public static boolean removeGroupMembersNickname(String roomId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbConnectionManager.getConnection();
            pstmt = conn.prepareStatement(REMOVE_GROUP_MEMBERS_NICKNAME);
            pstmt.setString(1, roomId);
            return pstmt.executeUpdate() >= 0;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("removeGroupMembersNickname error：" + e.getMessage());
        } finally {
            DbConnectionManager.closeConnection(null, pstmt, conn);
        }
        return false;
    }

    /**
     * 获取用户群昵称
     */
    public static String getMemberGroupNickNameByRoomIdAndUserId(String userId, String roomId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbConnectionManager.getConnection();
            pstmt = conn.prepareStatement(GET_MEMBER_GROUP_NICKNAME);
            pstmt.setString(1, userId);
            pstmt.setString(2, roomId);
            ResultSet rs = pstmt.executeQuery();
            String nickName = null;
            while (rs.next()) {
                nickName = rs.getString(1);
            }
            return nickName;
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("getMemberGroupNickNameByRoomIdAndUserId error：" + e.getMessage());
        } finally {
            DbConnectionManager.closeConnection(null, pstmt, conn);
        }
        return null;
    }

    /**
     * 从数据库中删除用户
     *
     * @param userJid
     * @return
     */
    public static boolean removeMember(MUCRoom room, String userJid) {
        MUCPersistenceManager.removeAffiliationFromDB(room, XMPPServer.getInstance().createJID(userJid, null), MUCRole.Affiliation.member);
        return true;
    }



    // =========================== 新加   =============================

    /**
	 * 根据用户Jid查询用户所在的所有房间信息
	 */
   /* public static List<Map<String, String>> getMUCInfo(String jid) {

        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        String sql = "select ofMucRoom.serviceID, ofMucRoom.name, ofMucRoom.roomid ,ofMucMember.nickname from "
                + "ofMucRoom join ofMucMember on ofMucRoom.roomID = ofMucMember.roomID and ofMucMember.jid = ?";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Map<String, String> map = null;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, jid);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                map = new HashMap<String, String>();
                map.put("serviceID", resultSet.getString(1));
                map.put("name", resultSet.getString(2));
                map.put("roomid", resultSet.getString(3));
                map.put("nickname", resultSet.getString(4));
                list.add(map);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        return list;
    }*/



    /**
     * 根据房间id获取房间内所有好友的信息
     */
    public static List<Map<String, String>> getRoomInfo(String roomId) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        String sql = "select jid, nickname from ofMucMember where roomID = ?";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Map<String, String> map;
        try {
            connection = DbConnectionManager.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, roomId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                map = new HashMap<>();
                map.put("jid", resultSet.getString(1));
                map.put("nickname", resultSet.getString(2));
                list.add(map);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            DbConnectionManager.closeConnection(resultSet, statement,
                    connection);
        }
        return list;
    }

}
